﻿//********************************************************************************//
//创建日期：<创建日期，2016-04-24>
//创建作者：<郭登杰，guodengjie@shanghai3h.com>
//功能说明：其他管理相关
//********************************************************************************//
using System;
using System.Collections.Generic;
using Newtonsoft.Json;
using Entity;
using System.Web;
using System.Web.SessionState;
using System.Text;
using System.IO;
using Web.Class;
using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace Web.ashx.QSQT
{
    public class QiTa : IHttpHandler, IRequiresSessionState
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";
            if (context.Request["op"] == "CaiGuanJZXD")
            {
                int ym = int.Parse(context.Request["ym"]);
                string printdate = context.Request["printdate"];
                string returndate = context.Request["returndate"];
                List<QSQTCaiGuanJZXD> list = new DALQSQTCaiGuanJZXD().Get(ym, printdate, returndate, null);
                context.Response.Write(JsonConvert.SerializeObject(list));
            }
            else if (context.Request["op"] == "CaiGuanJZXD_Export")
            {
                string filename = "采灌井征询单.xls";
                context.Response.ContentEncoding = System.Text.Encoding.UTF8;
                context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8).ToString());
                context.Response.ContentType = "application/ms-excel;charset=UTF-8";
                int ym = int.Parse(context.Request["ym"]);
                string printdate = context.Request["printdate"];
                string returndate = context.Request["returndate"];
                string yhhs = context.Request["yhhs"];
                List<QSQTCaiGuanJZXD> list = new DALQSQTCaiGuanJZXD().Get(ym, printdate, returndate, yhhs);
                StringBuilder str = new StringBuilder();
                str.Append("<style type='text/css'>td{border:0.5px solid gray;}</style><table><thead><tr><th>用户号</th><th>用户名</th><th>年</th><th>返回日期</th><th>打印日期</th><th>邮政编码</th><th>用户地址</th></tr>");
                string tr = "<tr><td>objYHH</td><td>objYHM</td><td>objYM</td><td>objReturnDate</td><td>objPrintDate</td><td>objYouBian</td><td>objDiZhi</td></tr>";
                foreach (var item in list)
                {
                    string tempTr = tr.Replace("objYHH", item.S_YONGHUH)
                        .Replace("objYHM", item.S_YONGHUM)
                        .Replace("objYM", item.I_YM.ToString())
                        .Replace("objReturnDate", item.S_RETURNDATE)
                        .Replace("objPrintDate", item.S_PRINTDATE)
                        .Replace("objYouBian", item.S_YOUBIAN)
                        .Replace("objDiZhi", item.S_DIZHI);
                    str.Append(tempTr);
                }
                context.Response.Write(str.ToString());
            }
            else if (context.Request["op"] == "DAYINSJDC")
            {
                int zwym = int.Parse(context.Request["zwym"]);
                int type = int.Parse(context.Request["type"]);
                int quyu = int.Parse(context.Request["quyu"]);
                List<QSQTDaYinSJDC> list = new DALQSQTDaYinSJDC().Get(zwym, type, quyu);
                context.Response.Write(JsonConvert.SerializeObject(list));
            }/*
            else if (context.Request["op"] == "DAYINSJDC_Export")
            {
                int zwym = int.Parse(context.Request["zwym"]);
                int type = int.Parse(context.Request["type"]);
                int quyu = int.Parse(context.Request["quyu"]);
                string filename = "SJ";
                string typeStr = (type == 0 ? "_TZS" : type == 1 ? "_DZZD" : type == 2 ? "_FDZZD" : type == 3 ? "_YHZD" : "");
                string quyuStr = (quyu == 0 ? "" : quyu == 1 ? "_CM" : quyu == 2 ? "_CX" : quyu == 3 ? "_HS" : "");
                string riqiStr = "(" + DateTime.Now.ToString("yyyy-MM-dd") + ")";
                filename = filename + typeStr + quyuStr + zwym.ToString() + riqiStr + ".xls";
                List<QSQTDaYinSJDC> list = new DALQSQTDaYinSJDC().Get(zwym, type, quyu);
                if (type == 3)
                {
                    filename = "地下水水费数据" + DateTime.Now.ToString("yyyyMMdd");
                    decimal jesum = 0;
                    foreach (var item in list)
                    {
                        jesum = jesum + Convert.ToDecimal(item.JINE);
                    }
                    filename = filename + "(" + list.Count.ToString() + "笔、" + jesum.ToString("0.00") + "元）【账务年月" + zwym.ToString() + "】.xls";
                }
                StringBuilder str = new StringBuilder();
                str.Append("<style type='text/css'>td{border:1px solid gray} .d{font-size:15px;text-align:left}</style><table><thead><tr><th>水量年</th><th>水量月</th><th>用户号</th><th style='width:400px'>用户名</th><th>用户地址</th><th>邮编</th><th>银行账号</th>" +
                    "<th>开户户名</th><th>开户银行</th><th>本月抄码</th><th>开票数</th><th>单价</th><th>金额</th><th>大写金额</th><th>费用编号</th><th>缴款人</th><th>托收标志</th><th>费用类型</th><th>备注</th><th>最后付款日期</th><th>联系人</th><th>财务户名</th></tr>");
                if (type == 0)
                {
                    str.Replace("备注", "老表号");
                }
                else
                {
                    str.Replace("<th>最后付款日期</th>", "");
                }
                string tr = "<tr><td class='d'>objY</td><td class='d'>objM</td><td class='d'>YHH</td><td class='d'>YHM</td><td class='d'>DIZHI</td><td class='d'>YOUBIAN</td><td class='d' style='vnd.ms-excel.numberformat:@'>YHZH</td>" +
                          "<td class='d'>KHHM</td><td class='d'>KHYH</td><td class='d'>BYCM</td><td class='d'>KPS</td><td class='d' style='vnd.ms-excel.numberformat:@'>DANJIA</td><td class='d' style='vnd.ms-excel.numberformat:@'>JINE</td><td class='d'>DAXIEJE</td>" +
                          "<td class='d'>ZDBH</td><td class='d'>JIAOKUANR</td><td class='d'>TSBZ</td><td class='d'>FYLX</td><td class='d'>objBZ</td><td class='d' style='vnd.ms-excel.numberformat:@'>XJRQ</td><td class='d'>LXR</td><td class='d'>CWHM</td></tr>";
                if (type == 0)
                {
                }
                else
                {
                    tr = tr.Replace("<td class='d' style='vnd.ms-excel.numberformat:@'>XJRQ</td>", "");
                }
                foreach (var item in list)
                {
                    string tempTr = tr.Replace("objY", item.Y)
                        .Replace("objM", item.M)
                        .Replace("YHH", item.YHH)
                        .Replace("YHM", item.YHM)
                        .Replace("DIZHI", item.DIZHI)
                        .Replace("YOUBIAN", item.YOUBIAN)
                        .Replace("YHZH", item.YHZH)
                        .Replace("KHHM", item.KHHM)
                        .Replace("KHYH", item.KHYH)
                        .Replace("BYCM", item.BYCM)
                        .Replace("KPS", item.KPS)
                        .Replace("DANJIA", Convert.ToDecimal(item.DANJIA).ToString("0.00"))
                        .Replace("JINE", Convert.ToDecimal(item.JINE).ToString("0.00"))
                        .Replace("DAXIEJE", item.DAXIEJE)
                        .Replace("ZDBH", item.ZDBH)
                        .Replace("JIAOKUANR", item.JIAOKUANR)
                        .Replace("TSBZ", item.TSBZ)
                        .Replace("FYLX", item.FYLX)
                        .Replace("objBZ", item.BZ)
                        .Replace("LXR", item.LXR)
                        .Replace("CWHM", item.CWHM)
                        .Replace("XJRQ", item.XJRQ.DateString);
                    str.Append(tempTr);
                }
                context.Response.ContentEncoding = System.Text.Encoding.UTF8;
                context.Response.AppendHeader("Content-Disposition", "inline;filename=" + HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8).ToString());
                context.Response.ContentType = "application/ms-excel;charset=UTF-8";
                context.Response.Write(str.ToString());
            }*/
            else if (context.Request["op"] == "DAYINSJDC_Export")
            {
                int zwym = int.Parse(context.Request["zwym"]);
                int type = int.Parse(context.Request["type"]);
                int quyu = int.Parse(context.Request["quyu"]);
                string filename = "SJ";
                string typeStr = (type == 0 ? "_TZS" : type == 1 ? "_DZZD" : type == 2 ? "_FDZZD" : type == 3 ? "_YHZD" : "");
                string quyuStr = (quyu == 0 ? "" : quyu == 1 ? "_CM" : quyu == 2 ? "_CX" : quyu == 3 ? "_HS" : "");
                string riqiStr = "(" + DateTime.Now.ToString("yyyy-MM-dd") + ")";
                filename = filename + typeStr + quyuStr + zwym.ToString() + riqiStr + ".xls";
                List<QSQTDaYinSJDC> list = new DALQSQTDaYinSJDC().Get(zwym, type, quyu);
                if (type == 3)
                {
                    filename = "地下水水费数据" + DateTime.Now.ToString("yyyyMMdd");
                    decimal jesum = 0;
                    foreach (var item in list)
                    {
                        jesum = jesum + Convert.ToDecimal(item.JINE);
                    }
                    filename = filename + "(" + list.Count.ToString() + "笔、" + jesum.ToString("0.00") + "元）【账务年月" + zwym.ToString() + "】.xls";
                }
                IWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet();
                IRow headerRow = sheet.CreateRow(0);   
                //设定列
                 int cellCount = 22;
                List<string> headCellNames = new List<string>();
                headCellNames.AddRange(new string[] { "水量年", "水量月", "用户号", "用户名", "用户地址", "邮编", "银行账号", "开户户名", "开户银行", "本月抄码" 
                                                       ,"开票数","单价","金额","大写金额","费用编号","缴款人","托收标志","费用类型","老表号","最后付款日期","联系人","财务户名"});
                if (type!=0)
                {
                    headCellNames[18] = "备注";
                    headCellNames.RemoveAt(19);
                    cellCount = 21;
                }
                     
                // handling header.  
                for (int i = 0; i < cellCount; i++)
                {
                    headerRow.CreateCell(i);
                    headerRow.GetCell(i).SetCellValue(headCellNames[i]);
                }
                //headerRow.GetCell(2).SetCellType(CellType.Numeric);
                int j = 1;
                
                foreach (var item in list)
                {
                    IRow dataRow = sheet.CreateRow(j);
                    int k = 0;
                    dataRow.CreateCell(k++).SetCellValue(item.Y);
                    dataRow.CreateCell(k++).SetCellValue(item.M);
                    dataRow.CreateCell(k++).SetCellValue(item.YHH);
                    dataRow.CreateCell(k++).SetCellValue(item.YHM);
                    dataRow.CreateCell(k++).SetCellValue(item.DIZHI);
                    dataRow.CreateCell(k++).SetCellValue(item.YOUBIAN);
                    dataRow.CreateCell(k++).SetCellValue(item.YHZH);
                    dataRow.CreateCell(k++).SetCellValue(item.KHHM);
                    dataRow.CreateCell(k++).SetCellValue(item.KHYH);
                    dataRow.CreateCell(k++).SetCellValue(item.BYCM);
                    dataRow.CreateCell(k++).SetCellValue(item.KPS);
                    dataRow.CreateCell(k++).SetCellValue(Convert.ToDecimal(item.DANJIA).ToString("0.00"));
                    dataRow.CreateCell(k++).SetCellValue(Convert.ToDecimal(item.JINE).ToString("0.00"));
                    dataRow.CreateCell(k++).SetCellValue(item.DAXIEJE);
                    dataRow.CreateCell(k++).SetCellValue(item.ZDBH);
                    dataRow.CreateCell(k++).SetCellValue(item.JIAOKUANR);
                    dataRow.CreateCell(k++).SetCellValue(item.TSBZ);
                    dataRow.CreateCell(k++).SetCellValue(item.FYLX);
                    dataRow.CreateCell(k++).SetCellValue(item.BZ);
                    if (type == 0)
                    {
                        dataRow.CreateCell(k++).SetCellValue(item.XJRQ.DateString);
                    }
                    dataRow.CreateCell(k++).SetCellValue(item.LXR);
                    dataRow.CreateCell(k++).SetCellValue(item.CWHM);
                    j++;
                }
                context.Response.ContentEncoding = System.Text.Encoding.UTF8;
                context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8).ToString());
                context.Response.AppendHeader("Content-Transfer-Encoding","binary");
                context.Response.ContentType = "application/ms-excel;charset=UTF-8";
                MemoryStream ms = new MemoryStream();
                workbook.Write(ms);
                context.Response.BinaryWrite(ms.GetBuffer());
                context.Response.End();
            }
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}